/******************************************************************************
 * @File name   :      MaintainMemberDaoImpl.java
 *
 * @Author      :      ANMA
 *
 * @Date        :      Jul 20, 2015
 *
 * @Copyright Notice: 
 * Copyright (c) 2014 DMS, Inc. All  Rights Reserved.
 * This software is published under the terms of the DMS Software
 * License version 1.0, a copy of which has been included with this
 * distribution in the LICENSE.txt file.
 * 
 * ----------------------------------------------------------------------------
 * Date                         Who         Version         Comments
 * Jul 20, 2015 6:56:59 PM    ANMA      1.0            Initial Version
 *****************************************************************************/
package com.cap.bts.crmdp.dealer.member.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.inject.Inject;

import org.apache.poi.hssf.record.formula.functions.T;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.cap.bts.commonfunc.common.util.UserUtil;
import com.cap.bts.crmdp.dealer.member.dao.IMaintainMemberDao;
import com.cap.bts.crmdp.dealer.member.domain.MemberInfo;
import com.cap.bts.crmdp.dealer.member.dto.MemberQueryDto;
import com.cap.bts.crmdp.dealer.member.dto.MemberResultsDto;
import com.cap.bts.crmdp.dealer.member.dto.WechatMemberQueryDto;
import com.cap.bts.framework.common.dao.callback.NativeQueryWithPagingJpaCallback;
import com.cap.bts.framework.common.dao.callback.QueryJpaCallback;
import com.cap.bts.framework.common.dao.impl.BaseDaoImpl;
import com.cap.bts.framework.common.exception.AppException;
import com.cap.bts.framework.common.util.Pager;
import com.cap.bts.framework.common.util.Utils;
import com.cap.bts.framework.common.util.WhereConditionGenerator;

/**
 * 
 */
@Repository("maintainMemberDao")
public class MaintainMemberDaoImpl extends BaseDaoImpl<MemberInfo, Long> implements IMaintainMemberDao {

	@Inject
	public MaintainMemberDaoImpl(@Value("com.cap.bts.crmdp.dealer.member.domain.MemberInfo") Class<MemberInfo> clazz) {
		super(clazz);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getMemberAndCarPlateByDto(MemberQueryDto dto) throws AppException {
		String select = "select ";
		String alias = " ) alias ";
		String count = "  count(1) from (";
		String union = " union ";
		
		String hql = " minfo.MOBILE_PHONE, minfo.MEMBER_NAME, cinfo.CAR_PLATE, minfo.CREATE_DATE, cinfo.MEMBER_STATUS, minfo.ID,cinfo.ID as carId";

		String fromStr = " from TM_RAW_MEMBER minfo INNER JOIN TM_RAW_CAR_PLATE cinfo ON minfo.ID = cinfo.MEMBER_ID";
		String whereStr = " where 1=1 ";
		String orderHql = " order by alias.ID desc";
		
		WhereConditionGenerator conditionmemberinfo = new WhereConditionGenerator(whereStr);
		WhereConditionGenerator conditionCarPlate = new WhereConditionGenerator(whereStr);
		
		StringBuilder sqlStr = new StringBuilder();
		StringBuilder countStr = new StringBuilder();
		
		
		if(Utils.notNullAndNegative(dto.getDealerId())){
			fromStr += " and minfo.DEALER_ID=" + dto.getDealerId();
		}
		
		countStr.append(select);
		countStr.append(count);
		countStr.append(select);
		countStr.append(hql);
		countStr.append(fromStr);
		
		
		sqlStr.append(select);
		sqlStr.append(" * from (");
		sqlStr.append(select);
		sqlStr.append(hql);
		sqlStr.append(fromStr);
		
		if (Utils.notEmpty(dto.getMobilePhoneOrCarPlate())) {

			conditionmemberinfo.andLike("minfo.MOBILE_PHONE", "%", dto.getMobilePhoneOrCarPlate(), "%");

			conditionCarPlate.andLike("cinfo.CAR_PLATE", "%", dto.getMobilePhoneOrCarPlate(), "%");
			
			sqlStr.append(conditionmemberinfo.toString()).append(union).append(select).append(hql).append(fromStr).append(conditionCarPlate.toString());

			countStr.append(conditionmemberinfo.toString()).append(union).append(select).append(hql).append(fromStr).append(conditionCarPlate.toString());
		}else if(Utils.notEmpty(dto.getMobilePhone()) && Utils.notEmpty(dto.getCarPlate())){
			// 手机+车牌不嫩重复。根据手机和车牌查询客户，若存在说明是老车老客户，
			conditionmemberinfo.andLike("minfo.MOBILE_PHONE", "%", dto.getMobilePhone(), "%");
			//conditionmemberinfo.andLike("minfo.MEMBER_NAME", "%", dto.getMemberName(), "%")
			conditionmemberinfo.andLike("cinfo.CAR_PLATE", "%", dto.getCarPlate(), "%");
			sqlStr.append(conditionmemberinfo.toString());
			countStr.append(conditionmemberinfo.toString());
		}else{
			sqlStr.append(whereStr);
		}

		sqlStr.append(alias);	
		sqlStr.append(orderHql);	
		countStr.append(alias);

		Pager page = dto.getPager();
		
		page.setPageSize(10);

		return getJpaTemplate().executeFind(new NativeQueryWithPagingJpaCallback<Object[]>(
				sqlStr.toString(), countStr.toString(), dto.getPager()));
	}
	
   
	@Override
	public List<MemberResultsDto> getMemberByMobilePhoneOrMemberId(MemberQueryDto dto) throws AppException {

		String hql = "select new com.cap.bts.crmdp.dealer.member.dto.MemberResultsDto(minfo.id, minfo.memberName, minfo.mobilePhone, minfo.dealerId) ";
		String fromStr = " from MemberInfo minfo";
		String whereStr = " where 1=1 ";
		String orderHql = " order by minfo.id desc";
		StringBuilder sqlStr = new StringBuilder();

		WhereConditionGenerator condition = new WhereConditionGenerator(whereStr);

		if (Utils.notEmpty(dto.getMobilePhone())){
			condition.andLike("minfo.mobilePhone", "%", dto.getMobilePhone(), "%");
		}
		
		if (Utils.notNullAndNegative(dto.getId())){
			condition.and("minfo.id", "=", dto.getId());
		}

		sqlStr.append(hql);	
		sqlStr.append(fromStr);	
		sqlStr.append(condition.toString());	
		if(Utils.notNullAndNegative(dto.getDealerId())){
			sqlStr.append(" and minfo.dealerId=" + dto.getDealerId());
		}
		sqlStr.append(orderHql);	

		List<MemberResultsDto> listDto = getJpaTemplate().executeFind(new QueryJpaCallback<List<MemberResultsDto>>(sqlStr.toString()));

		if (listDto != null && !listDto.isEmpty()) {
			return listDto;
		}
		return new ArrayList<MemberResultsDto>();
	}

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年8月4日
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainMemberDao#getMemberByCarPlate(com.cap.bts.crmdp.dealer.member.dto.MemberQueryDto)
    **/
    @Override
    public List<MemberResultsDto> getMemberByCarPlate(MemberQueryDto dto) throws AppException {
        String hql = "select new com.cap.bts.crmdp.dealer.member.dto.MemberResultsDto(c.carPlate)";
        String fromStr = " from CarPlateInfo c";
        String whereStr = " where 1=1 ";
        StringBuilder sqlStr = new StringBuilder();
        WhereConditionGenerator condition = new WhereConditionGenerator(whereStr);
        condition.and("c.carPlate", "=", dto.getCarPlate());
        sqlStr.append(hql); 
        sqlStr.append(fromStr); 
        sqlStr.append(condition.toString());
        List<MemberResultsDto> listDto = getJpaTemplate().executeFind(new QueryJpaCallback<List<MemberResultsDto>>(sqlStr.toString()));
        if (listDto != null && !listDto.isEmpty()) {
            return listDto;
        }
        return new ArrayList<MemberResultsDto>();
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年8月5日
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainMemberDao#updateMemberNameByMemberResultsDto(com.cap.bts.crmdp.dealer.member.dto.MemberResultsDto)
    **/
    @Override
    public boolean updateMemberNameByMemberResultsDto(MemberResultsDto memberResultsDto)
        throws AppException {
        try {
            String hql = "update MemberInfo info set info.memberName = ?1 where info.mobilePhone = ?2";
            this.updateByQUeryAndVaParam(hql, memberResultsDto.getMemberName(),memberResultsDto.getMobilePhone());
            return true;
        } catch (Exception e) {
            throw new AppException("updateMemberNameByMemberResultsDto", e);
        }
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年8月24日
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainMemberDao#getActivePackageCount(java.lang.Long, java.lang.Long, java.lang.Long)
    **/
    @Override
    public int getActivePackageCount(Long dealerId, Long memberId, Long carId) throws AppException {
        try {
            String hql = "select count(1) from tm_member_package_info packageInfo, tm_crm_package_definition b where packageInfo.ACTIVE_DEALER_ID="+dealerId
                + " and packageInfo.ACTIVE_MEMBER_ID="+memberId+" and packageInfo.ACTIVE_CAR_ID="+carId +" and packageInfo.PACKAGE_DEF_ID=b.ID";
            Map<String, Long> map = new HashMap<String, Long>();
            map.put("dealerId", dealerId);
            map.put("memberId", memberId);
            map.put("carId", carId);
            return this.countByNativeQuery(hql);
        } catch (Exception e) {
            throw new AppException("getActivePackageCount", e);
        }
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年9月1日
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainMemberDao#updateMemberMobileByMemberResultsDto(com.cap.bts.crmdp.dealer.member.dto.MemberResultsDto)
    **/
    @Override
    public boolean updateMemberMobileByMemberResultsDto(MemberResultsDto memberResultsDto)
        throws AppException {
        try {
            String hql = "update MemberInfo info set info.mobilePhone = ?1 where info.id = ?2";
            this.updateByQUeryAndVaParam(hql, memberResultsDto.getMobilePhone(),memberResultsDto.getId());
            return true;
        } catch (Exception e) {
            throw new AppException("updateMemberMobileByMemberResultsDto", e);
        }
    }

    @Override
    public MemberInfo getMemberInfoByMarsInfo(String customerId,String dealerId) {
        Map<String,Object> paraMap = new HashMap<String,Object>();
        paraMap.put("marsMemberId", customerId);
        paraMap.put("marsDealerId", dealerId);
        String hql = "from MemberInfo cinfo where cinfo.marsMemberId=:marsMemberId and cinfo.marsDealerId=:marsDealerId";
        List<MemberInfo> resultList = this.getListByQueryWithMap(hql, paraMap);
        if(resultList != null && !resultList.isEmpty()){
            return resultList.get(0);
        }else{
            return null;
        }
    
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年10月23日
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainMemberDao#getMemberByDealerId(java.lang.Long)
    **/
    @Override
    public List<MemberInfo> getMemberByDealerId(Long dealerId,String marsMemberId) throws AppException {
        String sql = "select * from tm_raw_member a where a.DEALER_ID = :dealerId "
                   + " and a.MARS_MEMBER_ID = :marsMemberId" ;
        
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("dealerId", dealerId);
        paramMap.put("marsMemberId", marsMemberId);
        
        return this.getListByNativeQueryWithMap(sql, paramMap);
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      Oct 27, 2015
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainMemberDao#getMemberByMarsInfo(java.lang.String, java.lang.String)
    **/
    @Override
    public List<MemberInfo> getMemberByMarsInfo(String marsDealerId, String marsMemberId)
        throws AppException {
        String sql = "select a.* from tm_raw_member a where a.MARS_DEALER_ID = :marsDealerId"
            + " and a.MARS_MEMBER_ID = :marsMemberId" ;
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("marsDealerId", marsDealerId);
        paramMap.put("marsMemberId", marsMemberId);
        
        return this.getListByNativeQueryWithMap(sql, paramMap);
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年11月18日
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainMemberDao#getMemberByDealerIdMemberIdMobilePhone(java.lang.Long, java.lang.Long, java.lang.String)
    **/
    @Override
    public List<MemberInfo> getMemberByDealerIdMemberIdMobilePhone(Long dealerId,String mobile) throws AppException {
        String sql = "select * from tm_raw_member a "
            + " where a.DEALER_ID = "+ dealerId
            + " and a.MOBILE_PHONE = '" + mobile + "'";
        return this.getListByNativeQuery(sql);
    }
    
    
    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年10月23日
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainMemberDao#getMemberByDealerId(java.lang.Long)
    **/
    @Override
    public List<MemberInfo> getMemberByMarsMemberId(String marsMemberId) throws AppException {
        String sql = "select * from tm_raw_member a where a.MARS_MEMBER_ID = :marsMemberId" ;
        
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("marsMemberId", marsMemberId);
        
        return this.getListByNativeQueryWithMap(sql, paramMap);
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年11月24日
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainMemberDao#getWechatMemberOrCarPlateByDto(com.cap.bts.crmdp.dealer.member.dto.WechatMemberQueryDto)
    **/
    @Override
    public List<Object[]> getWechatMemberOrCarPlateByDto(WechatMemberQueryDto dto)
        throws AppException {
        String sql = "select a.member_name,b.car_plate,a.mobile_phone,a.open_id"
            + " from tm_wechat_member a,tm_wechat_member_car b"
            + " where a.open_id=b.open_id"
            + " and NOT EXISTS(SELECT d.MEMBER_NAME,d.MOBILE_PHONE,c.car_plate"
                + " from tm_raw_car_plate c,tm_raw_member d"
                + " WHERE c.MEMBER_ID=d.id"
                + " AND d.DEALER_ID="+UserUtil.getCurrentUser().getDealerId()
                + " and c.car_plate=b.car_plate)"
            + " AND (a.mobile_phone = '"+dto.getMobilePhoneOrCarPlate()+"' or b.car_plate = '"+dto.getMobilePhoneOrCarPlate()+"')";
        String count = "select count(1) from (select a.member_name,b.car_plate,a.mobile_phone,a.id"
            + " from tm_wechat_member a,tm_wechat_member_car b"
            + " where a.open_id=b.open_id"
            + " and NOT EXISTS(SELECT d.MEMBER_NAME,d.MOBILE_PHONE,c.car_plate"
                + " from tm_raw_car_plate c,tm_raw_member d"
                + " WHERE c.MEMBER_ID=d.id"
                + " AND d.DEALER_ID="+UserUtil.getCurrentUser().getDealerId()
                + " and c.car_plate=b.car_plate)"
            + " AND (a.mobile_phone = '"+dto.getMobilePhoneOrCarPlate()+"' or b.car_plate = '"+dto.getMobilePhoneOrCarPlate()+"')) c";
        Pager page = dto.getPager();
        page.setPageSize(5);
        return this.getJpaTemplate().executeFind(new NativeQueryWithPagingJpaCallback<T>(sql, count, dto.getPager()));
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年11月24日
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainMemberDao#getAllWechatMemberOrCarPlateByDto()
    **/
    @Override
    public List<Object[]> getAllWechatMemberOrCarPlateByDto(WechatMemberQueryDto dto) throws AppException {
        String sql = "select a.member_name,b.car_plate,a.mobile_phone,a.id"
            + " from tm_wechat_member a,tm_wechat_member_car b"
            + " where a.open_id=b.open_id";
        String count = "select count(1) from(select a.member_name,b.car_plate,a.mobile_phone,a.id"
            + " from tm_wechat_member a,tm_wechat_member_car b"
            + " where a.open_id=b.open_id) c";
        Pager page = dto.getPager();
        page.setPageSize(5);
        return this.getJpaTemplate().executeFind(new NativeQueryWithPagingJpaCallback<T>(sql, count, dto.getPager()));
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年11月30日
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainMemberDao#checkWechatMemberOrCarPlateByDto(java.lang.String)
    **/
    @Override
    public List<MemberInfo> checkWechatMemberOrCarPlateByDto(String mobilePhoneOrCarPlate)
        throws AppException {
        String sql = "SELECT d.*"
                + " from tm_raw_car_plate c,tm_raw_member d"
                + " WHERE c.MEMBER_ID=d.id"
                + " AND d.DEALER_ID="+UserUtil.getCurrentUser().getDealerId()
            + " AND (d.mobile_phone = '"+mobilePhoneOrCarPlate+"' or c.car_plate = '"+mobilePhoneOrCarPlate+"')";
        return this.getListByNativeQuery(sql);
    }

	@Override
	public List<MemberInfo> getMemberByDealerIdAndMARSMemberId(Long dealerId, String MARSMemberId) throws AppException {
		String sql = "select * from tm_raw_member a "
	            + " where a.DEALER_ID = "+ dealerId
	            + " and a.MARS_MEMBER_ID = '" + MARSMemberId + "'";
	        return this.getListByNativeQuery(sql);
	}

	@Override
	public List<MemberResultsDto> getMemberByCarPlateForThisDealer(MemberQueryDto dto) throws AppException {
		String hql = "select new com.cap.bts.crmdp.dealer.member.dto.MemberResultsDto(d.id,d.memberName,c.carPlate,d.mobilePhone)";
        String fromStr = " from CarPlateInfo c,MemberInfo d";
        String whereStr = " where 1=1 and c.memberId = d.id and d.dealerId = '" + UserUtil.getCurrentUser().getDealerId() + "'";
        StringBuilder sqlStr = new StringBuilder();
        WhereConditionGenerator condition = new WhereConditionGenerator(whereStr);
        condition.and("c.carPlate", "=", dto.getCarPlate());
        sqlStr.append(hql); 
        sqlStr.append(fromStr); 
        sqlStr.append(condition.toString());
        List<MemberResultsDto> listDto = getJpaTemplate().executeFind(new QueryJpaCallback<List<MemberResultsDto>>(sqlStr.toString()));
        if (listDto != null && !listDto.isEmpty()) {
            return listDto;
        }
        return new ArrayList<MemberResultsDto>();
	}

}
